<?php
$pghostSource="host=www.south.rmutsv.ac.th port=5432 dbname=edoc user=mooz password=moo2520";
//$pghostSource="host=localhost port=5432 dbname=edoc user=mooz password=moo2520";
$pghostDest="host=localhost port=5432 dbname=rmutsv_south user=mooz password=moo2520";

$departmentMAP = array();
$userMAP = array();
$positionMAP = array();
$pnameMAP = array();
$edoc_typeMAP = array();
$edocMAP = array();
$kseanMAP = array();


function getMAPo2n($map,$old){
    foreach($map as $dat){
        if($old==0) return 0;
        if($dat['oldID']==$old) return $dat['newID'];
    }
}

function getPG($query,$dbx){
	$dbconn = pg_connect($dbx)
    	or die('Could not connect: ' . pg_last_error());

	$result = pg_query($query) or die('Query failed: ' . pg_last_error());
	$ret = array();
	$index = 0;

	while($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
		$ret[$index] = $line;
		$index = $index+1;
	}

	pg_free_result($result);
	pg_close($dbconn);
	
	return $ret;
}

function updatePG($query,$dbx){
	$dbconn = pg_connect($dbx)
		or die('Could not connect: ' . pg_last_error());
		
	$result = pg_exec($dbconn,$query);

	pg_close($dbconn);
	
	return $result;
}

function migratDepartment($source,$dest){
    $departmentMAP = array();
    $departOld = getPG("select * from department;",$source);
    $i = 0;
    foreach($departOld as $dat){
        
        $oldID = $dat['depart_id'];
        if($oldID!=1){
            $newID = getPG("select nextval('department_depart_id_seq');",$dest);
            $newID = $newID[0]['nextval'];
        }else{
            $newID = 1;
        }
        
        $departmentMAP[$i]['oldID'] = $oldID;
        $departmentMAP[$i]['newID'] = $newID;
        
        $i = $i+1;
        
        if($oldID==1) continue;
        updatePG("create sequence depart_recieve_seq_".$newID,$dest);
        updatePG("insert into department (depart_id,depart_name) values ($newID,'".$dat['depart_name']."');",$dest);
    }
    
    return $departmentMAP;
}

function genDepartHier($source,$dest){
    $depart = getPG("select * from department",$dest);
    foreach($depart as $dat){
        if($dat['depart_id']==1) continue;
        updatePG("insert into department_hier (head_depart_id,under_depart_id) values (".$dat['depart_id'].",".$dat['depart_id'].")",$dest);
    }
}

function migratUser($source,$dest,$departmentMAP){
    $userMAP = array();
    
    $userOld = getPG("select * from edoc_user;",$source);
    $i = 0;
    foreach($userOld as $dat){
        $oldID = $dat['user_id'];
        if($oldID!=1){
            $newID = getPG("select nextval('edoc_user_user_id_seq');",$dest);
            $newID = $newID[0]['nextval'];
        }else{
            $newID = 1;
        }
        
        $userMAP[$i]['oldID'] = $oldID;
        $userMAP[$i]['newID'] = $newID;
        
        $i = $i+1;
        
        if($oldID==1) continue;
        updatePG("insert into edoc_user (user_id,user_name,user_passwd,read,write,user_type,secret,depart_id) values ($newID,'".$dat['user_name']."','".$dat['user_passwd']."',".$dat['read'].",".$dat['write'].",".$dat['user_type'].",".$dat['secret'].",".getMAPo2n($departmentMAP,$dat['depart_id']).");",$dest);
    }
    
    return $userMAP;
}

function migratPosition($source,$dest,$departmentMAP){
    $positionMAP = array();
    $positionOld = getPG("select * from position;",$source);
    $i = 0;
    foreach($positionOld as $dat){
        $oldID = $dat['position_id'];
        $newID = getPG("select nextval('position_position_id_seq');",$dest);
        $newID = $newID[0]['nextval'];
        
        $positionMAP[$i]['oldID'] = $oldID;
        $positionMAP[$i]['newID'] = $newID;
        
        $i = $i+1;
        
        updatePG("insert into position (position_id,position_name,depart_id) values ($newID,'".$dat['position_name']."',".getMAPo2n($departmentMAP,$dat['depart_id']).");",$dest);
    }
    
    return $positionMAP;
}

function migratPname($source,$dest,$positionMAP){
    $pnameMAP = array();
    $pnameOld = getPG("select * from pname;",$source);
    $i = 0;
    foreach($pnameOld as $dat){
        $oldID = $dat['pname_id'];
        $newID = getPG("select nextval('pname_pname_id_seq');",$dest);
        $newID = $newID[0]['nextval'];
        
        $pnameMAP[$i]['oldID'] = $oldID;
        $pnameMAP[$i]['newID'] = $newID;
        
        $i = $i+1;
        
        updatePG("insert into pname (pname_id,pname_name,position_id,pname_status) values ($newID,'".$dat['pname_name']."',".getMAPo2n($positionMAP,$dat['position_id']).",'".$dat['pname_status']."');",$dest);
    }
    
    return $pnameMAP;
}

function migratEdoc_type($source,$dest,$departmentMAP){
    $edoc_typeMAP = array();
    $edoc_typeOld = getPG("select * from edoc_type;",$source);
    $i = 0;
    foreach($edoc_typeOld as $dat){
        $oldID = $dat['edoc_type_id'];
        $newID = getPG("select nextval('edoc_type_edoc_type_id_seq');",$dest);
        $newID = $newID[0]['nextval'];
        
        $edoc_typeMAP[$i]['oldID'] = $oldID;
        $edoc_typeMAP[$i]['newID'] = $newID;
        
        $i = $i+1;
        
        updatePG("insert into edoc_type (edoc_type_id,edoc_type_name,depart_id) values ($newID,'".$dat['edoc_type_name']."',".getMAPo2n($departmentMAP,$dat['depart_id']).");",$dest);
    }
    
    return $edoc_typeMAP;
}

function migratEdoc($source,$dest,$departmentMAP,$edoc_typeMAP){
    $edocMAP = array();
    
	$dbconn = pg_connect($source)
    	or die('Could not connect: ' . pg_last_error());

	$result = pg_query("select * from edoc order by edoc_id;") or die('Query failed: ' . pg_last_error());

    $i = 0;

	while($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
		$dat = $line;
    
        $oldID = $dat['edoc_id'];
        $newID = getPG("select nextval('edoc_edoc_id_seq');",$dest);
        $newID = $newID[0]['nextval'];
        
        $edocMAP[$i]['oldID'] = $oldID;
        $edocMAP[$i]['newID'] = $newID;
        
        $i = $i+1;
        
        $owner_id = getOwnerID($dat['edoc_type_id'],$source);
        
        if($dat['pdf_name']!=""){
            $pdf = pg_unescape_bytea($dat['pdf']);
        }
        else $pdf = "";
        $pdf = pg_escape_bytea($pdf);
        
        $recieve_time = $dat['recieve_time'];
        if($recieve_time=="") $recieve_time = "00:00:00";
        
        updatePG("insert into edoc (edoc_id,doc_no,doc_date,sender,reciever,headline,comment,pdf,depart_send_id,depart_send_name_out,secret,rapid,pdf_name,pdf_type,depart_owner_id) values ($newID,'".$dat['doc_no']."','".$dat['doc_date']."','".$dat['sender']."','".$dat['reciever']."','".$dat['headline']."','".$dat['comment']."','{$pdf}',".getMAPo2n($departmentMAP,$dat['depart_send_id']).",'".$dat['depart_send_name_out']."',".$dat['secret'].",".$dat['rapid'].",'".$dat['pdf_name']."','".$dat['pdf_type']."',".getMAPo2n($departmentMAP,$owner_id).");",$dest);
        if(getMAPo2n($departmentMAP,$dat['depart_run_id'])!=0)
            updatePG("insert into edoc_run (edoc_id,depart_id) values ($newID,".getMAPo2n($departmentMAP,$dat['depart_run_id']).")",$dest);
        updatePG("insert into edoc_recieve (edoc_type_id,recieve_no,recieve_date,recieve_time,edoc_id) values (".getMAPo2n($edoc_typeMAP,$dat['edoc_type_id']).",'".$dat['recieve_no']."','".$dat['recieve_date']."','".$recieve_time."',$newID)",$dest);
    }
    
    pg_free_result($result);
	pg_close($dbconn);

    return $edocMAP;
}

function getOwnerID($edoc_type_id,$db){
    $r = getPG("select * from edoc_type where edoc_type_id=$edoc_type_id;",$db);
    return $r[0]['depart_id'];

}

function migratKsean($source,$dest,$pnameMAP,$edocMAP){
    $kseanMAP = array();
    
	$dbconn = pg_connect($source)
    	or die('Could not connect: ' . pg_last_error());

	$result = pg_query("select * from ksean;") or die('Query failed: ' . pg_last_error());

    $i = 0;

	while($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
		$dat = $line;
    
        $oldID = $dat['ksean_id'];
        $newID = getPG("select nextval('ksean_ksean_id_seq');",$dest);
        $newID = $newID[0]['nextval'];
        
        $kseanMAP[$i]['oldID'] = $oldID;
        $kseanMAP[$i]['newID'] = $newID;
        
        $i = $i+1;
        
        updatePG("insert into ksean (ksean_id,edoc_id,pname_id,ksean_txt,ksean_date) values ($newID,".getMAPo2n($edocMAP,$dat['edoc_id']).",".getMAPo2n($pnameMAP,$dat['pname_id']).",'".$dat['ksean_txt']."','".$dat['ksean_date']."');",$dest);
    }
    
    pg_free_result($result);
	pg_close($dbconn);
    
    return $kseanMAP;
}

$departmentMAP = migratDepartment($pghostSource,$pghostDest);
genDepartHier($pghostSource,$pghostDest);
$userMAP = migratUser($pghostSource,$pghostDest,$departmentMAP);
$positionMAP = migratPosition($pghostSource,$pghostDest,$departmentMAP);
$pnameMAP = migratPname($pghostSource,$pghostDest,$positionMAP);
$edoc_typeMAP = migratEdoc_type($pghostSource,$pghostDest,$departmentMAP);
$edocMAP = migratEdoc($pghostSource,$pghostDest,$departmentMAP,$edoc_typeMAP);
$kseanMAP = migratKsean($pghostSource,$pghostDest,$pnameMAP,$edocMAP);

echo "Successful !!!";
?>
